#!/usr/bin/python3
# -*- coding:utf-8 -*-
"""
@File: Unisql.py
@Author: lijk34925 
@Date: 2024/9/13 11:01
@desc: 请补充本模块功能说明
"""


import sqlparse
import re

from copy import copy
from datetime import date, timedelta
from qturboframe.lib.utils.variable import Variable

from BladeFerm.databases import DB_APP
from BladeFerm.databases.DealSql.EnvConfig import EnvConfig
from CommonScripts.otherUtils import random_str

'''
统一sql方案:
!!!现在均按字符串处理,即返回给sql都会带上单引号
基本语法:
<模式:方法参数,使用英文逗号分隔:储存变量名>
**储存变量名是为了后续再次使用这个值,使用-可以不储存
1.limit子句和rownum(貌似都是select才有,其他的不太会有,暂放)

2.nvl和ifnull:无感替换;

3.自增:
语法:<inc:库名,表名,字段名:储存变量名>
example:
<inc:hspbs,sys_relationgroup,relation_group_no:relation_group_no>
处理:查询一次数据库,若为null,返回0+1,否则返回x+1,并赋值给relation_group_no;

4.随机:
语法:<ran:包含类型(0-数字,a-小写英文,A-大写英文,使用|分隔),长度:储存变量名>
example:
<ran:0|a|A,20:serial_no>
处理:生成20位包含数字,小写英文,大写英文的随机字符串,并赋值给serial_no;

5.时间:
语法:<tim:时间:->
example:
<tim:2020-09-04 9:30:00.000:->
处理:mysql加上引号返回,oracle加上timestamp

6.查询:

7.设置变量
语法:<set:值:储存变量名>
example:
<set:123:key>
处理:返回123,并将123赋值给key

8.循环
语法:<for:范围:储存变量名>
example:
数字<for:1~10:key>
其他<for:1,2,3,a,b,c:->
重复<for:xxx*10:->
日期<for:2020-09-04~2020-10-01:-># todo:未兼容数据库?
处理:根据范围将一个sql扩展为多个sql,即循环10次的话会将sql根据循环变量生成10个,设置的变量会储存为列表

# todo:支持简单的计算,对于列表等类型支持下,转为in等方式
99.自定义变量:
语法:<key:变量名>
'''


class Unisql(object):
    '''
    统一sql
    '''
    # key:taskid|path
    # value:对象
    # 保证一次task中,同一个路径的sql脚本只实例化一次,让数据保持,第一次初始化加缓存,直到dml传入delete后删除缓存
    CACHE = {}

    @classmethod
    def __new__(cls, *args, **kwargs):
        '''
        为了实现blade一次调用,同一个脚本需要数据保持
        同一个taskid,同一个path的情况下,为单例,第二次直接取缓存,直到dml传入delete后删除缓存
        '''
        # 获取参数
        paramList = ['path','dml','env']
        posParam = dict([(k, v) for k, v in zip(paramList[:len(args[1:])],args[1:])])
        keyParam = kwargs
        defaultParam = {
            'dml': 'delete,insert,merge,update',
            'env': 'oracle'
        }
        param = {**defaultParam,**posParam,**keyParam}
        path = param['path']
        dml = param['dml']
        env = param['env']
        config = param['config']
        if dml == '':
            dml = 'delete,insert,merge,update'
        # 有选择的单例模式,当taskid和path存在的时候,走单例,否则重新创建对象
        _key = f'{Variable().taskid}|{path}'
        if _key in cls.CACHE:
            # 存在缓存
            if dml.lower()=='delete':
                # 删除缓存,不运行初始化,并返回对象
                _self=cls.CACHE.pop(_key)
                _self.__fillter(dml)
                return _self
            else:
                # 不删除缓存,不运行初始化,返回对象
                return cls.CACHE[_key]
        else:
            # 不存在缓存,需要生成对象,并初始化
            _self=super().__new__(cls)
            # 初始化
            _self.__init(path,dml,env,config)
            _self.__fillter(dml)
            # 加入缓存
            cls.CACHE[_key]=_self
            return _self

    def __init(self, path, dml='delete,insert,merge,update', env='oracle', config=None):
        '''
        自定义初始化
        '''
        # 自增变量{'user.table.column':1}
        self.inc = {}
        # 储存变量
        self.key = {}
        # 环境
        self._env = EnvConfig(env, config)
        self.env = self._env.env
        # 数据库连接配置
        self.dbconfig = self._env.dbConfig
        # 数据库类型
        self.db_type = self._env.dbType
        # 数据库连接
        self.db = DB_APP.get_dba()
        # 数据库兼容ob后的类型
        if self.db_type == 'oceanbase':
            self._db_type = 'oracle'
        else:
            self._db_type = self.db_type
        self.dml = dml.lower().split(',')
        # sql结果,过程变量
        self._sqlList = []
        # sql结果,实际变量
        self.sqlList = []
        # 解析sql
        self.analysisSql(path)
        self.__split_insert_sql()
        for index, sql in enumerate(self._sqlList):
            _sql = sql
            # nvl处理
            _sql = self.handleNvl(_sql)
            # 自增处理
            _sql = self.handleIncrement(_sql)
            # 随机处理
            _sql = self.handleRandom(_sql)
            # 时间处理
            _sql = self.handleTime(_sql)
            # 设置处理
            _sql = self.handleSet(_sql)
            # 处理注释
            _sql = self.handleComment(_sql)
            # 回传
            self._sqlList[index]=_sql
        for index, sql in enumerate(copy(self._sqlList)):
            # 处理循环
            _sql_list = self.hangleFor(sql)
            # 回传
            self._sqlList[index] = _sql_list
        else:
            # 列表处理
            __sqlList = copy(self._sqlList)
            self._sqlList = []
            for i in __sqlList:
                self._sqlList += i
        for index, sql in enumerate(self._sqlList):
            # 需要最后一步!!!处理变量
            self._sqlList[index] = self.handleKey(sql)

    def __fillter(self,dml):
        '''
        处理dml
        '''
        self.dml=dml.lower().split(',')
        # 清空原先的列表
        self.sqlList.clear()
        # 处理dml
        for _sql in self._sqlList:
            _sql_format = sqlparse.format(_sql, reindent=True, keyword_case='lower')
            for _type in self.dml:
                if _sql_format.startswith(_type):
                    if _sql_format.endswith(';'):
                        self.sqlList.append(_sql_format[:-1])
                    else:
                        self.sqlList.append(_sql_format)
        #logger.info(f"统一sql===》{self.sqlList}")

    def __init__(self, path, dml='delete,insert,merge,update', env='oracle', config=None):
        pass

    def __iter__(self):
        return iter(self.sqlList)

    def __saveKey(self,key,value):
        '''
        储存key
        '''
        if key in self.key:
            raise RuntimeError(f'{key}已经定义过,重复定义')
        elif key=='-':
            pass
        else:
            self.key[key]=value

    def analysisSql(self, path):
        '''
        将该路径的sql解析为list
        '''
        # 读取文件
        sql_text = ""
        with open(path, 'r', encoding='utf-8') as f:
            lines = f.readlines()
            for row in lines:
                # 注释不需要
                if len(row) < 2 or row.startswith("--") or row.startswith("/*") or row.startswith("#"):
                    pass
                else:
                    sql_text += row
        if sql_text:
            self._sqlList = sqlparse.split(sql_text, encoding='utf-8')
        return self._sqlList

    def test_split_insert_sql(self):
        self.__split_insert_sql()

    def __split_insert_sql(self):
        if re.match(r"oracle", self.env, re.I):
            temp_list = list()
            for line in self._sqlList:
                line = self.handleComment(line)
                result = re.match(r"insert into(.*)values(.*?);$", line, re.I | re.DOTALL)
                if result:
                    keys = result.group(1)
                    valueStr = result.group(2)
                    r2 = re.findall(r"\((.*?)\),", valueStr + ',', flags=re.DOTALL)
                    if len(r2) > 0:
                        insertSql = f"insert into {keys.replace('`', '')} values "
                        for unit in r2:
                            currentSql = insertSql + "(" + unit + ");"
                            temp_list.append(currentSql)
                    else:
                        temp_list.append(line)
                else:
                    temp_list.append(line)
            self._sqlList = temp_list
        else:
            pass

    def handleNvl(self,sql):
        '''
        处理nvl
        oracle:ifnull-->nvl
        mysql:nvl-->ifnull
        oceanbase:根据类型替换,如上
        dm:不替换
        pg:???
        '''
        if self._db_type == 'oracle':
            pattern=r'(?i)ifnull'
            return re.sub(pattern, 'nvl', sql)
        elif self._db_type == 'mysql':
            pattern = r'(?i)nvl'
            return re.sub(pattern, 'ifnull', sql)
        else:
            return sql

    def handleIncrement(self,sql):
        '''
        处理自增
        '''
        def repl_func(matched_obj):
            '''
            正则替换函数
            '''
            matched_obj_dict=matched_obj.groupdict()
            owner=matched_obj_dict['owner']
            table=matched_obj_dict['table']
            column=matched_obj_dict['column']
            if f'{owner}.{table}.{column}' in self.inc:
                self.inc[f'{owner}.{table}.{column}']+=1
            else:
                self.inc[f'{owner}.{table}.{column}']=1
            key=matched_obj_dict['key']
            sql=f"select case when max(to_number({column})) is null then 0 "\
                f"else max(to_number({column})) "\
                f"end+{self.inc[f'{owner}.{table}.{column}']} "\
                f"as id from {owner}.{table}"
            _id=self.db.exec_sql(sql)[0]['ID']
            self.__saveKey(key,f'{_id}')
            return f'{_id}'

        pattern=r'(?i)<inc:(?P<owner>[^\s]+?),(?P<table>[^\s]+?),(?P<column>[^\s]+?):(?P<key>[^\s]+?)>'
        return re.sub(pattern,repl_func,sql)

    def handleRandom(self,sql):
        '''
        处理随机
        '''
        def repl_func(matched_obj):
            '''
            正则替换函数
            '''
            matched_obj_dict=matched_obj.groupdict()
            model=matched_obj_dict['model']
            length=int(matched_obj_dict['length'])
            key=matched_obj_dict['key']
            ran=random_str(length,model)
            self.__saveKey(key,ran)
            return f"'{ran}'"

        pattern=r'(?i)<ran:(?P<model>[^\s]+?),(?P<length>[0-9]+):(?P<key>[^\s]+?)>'
        return re.sub(pattern,repl_func,sql)

    def handleTime(self,sql):
        '''
        处理时间
        '''
        def repl_func(matched_obj):
            '''
            正则替换函数
            '''
            matched_obj_dict=matched_obj.groupdict()
            _time=matched_obj_dict['time']
            key=matched_obj_dict['key']
            if self._db_type == 'oracle':
                _time = f"TIMESTAMP '{_time}'"
                self.__saveKey(key, _time)
                return _time
            else:
                _time = f"{_time}"
                self.__saveKey(key, _time)
                return f"'{_time}'"

        pattern=r'(?i)<tim:(?P<time>\d{4}-\d{1,2}-\d{1,2}[ ]+[012]?\d:[0-6]?\d:[0-6]?\d[.\d]*):(?P<key>[^\s]+?)>'
        return re.sub(pattern,repl_func,sql)

    def handleSet(self,sql):
        '''
        处理设置变量
        '''
        def repl_func(matched_obj):
            '''
            正则替换函数
            '''
            matched_obj_dict=matched_obj.groupdict()
            value=matched_obj_dict['value']
            key=matched_obj_dict['key']
            self.__saveKey(key, value)
            return f"'{value}'"

        pattern=r'(?i)<set:(?P<value>[^:\n]+):(?P<key>[^\s]+?)>'
        return re.sub(pattern,repl_func,sql)

    def hangleFor(self,sql):
        '''
        处理循环
        '''
        def getRange(_range):
            if '~' in _range and '-' not in _range:
                # 数字模式
                start, end = _range.split('~')
                _range_list = [i for i in range(int(start), int(end) + 1)]
            elif '~' in _range and '-' in _range:
                # 日期模式
                start, end = _range.split('~')
                _start = start.split('-')
                _end = end.split('-')
                startDate = date(int(_start[0]), int(_start[1]), int(_start[2]))
                endDate = date(int(_end[0]), int(_end[1]), int(_end[2]))
                _range_list = [(startDate + timedelta(days=i)).strftime('%Y-%m-%d') for i in
                               range((endDate - startDate).days + 1)]
            elif '*' in _range:
                # 重复模式
                value, num = _range.split('*')
                _range_list = [value] * int(num)
            else:
                # 其他模式
                _range_list = _range.split(',')
            return _range_list

        def repl_func(i):
            def repl_func_inner(matched_obj):
                '''
                正则替换函数
                '''
                matched_obj_dict=matched_obj.groupdict()
                _range = matched_obj_dict['range']
                key = matched_obj_dict['key']
                _range_list = getRange(_range)
                if not i:
                    self.__saveKey(key, _range_list)
                return f"'{_range_list[i]}'"
            return repl_func_inner

        pattern=r'(?i)<for:(?P<range>\d+~\d+|[\w,]+|[\w_]+\*\d+|\d{4}-[01]?\d-[0123]?\d~\d{4}-[01]?\d-[0123]?\d):(?P<key>[^\s]+?)>'
        matched_obj=re.search(pattern,sql)
        if matched_obj:
            matched_obj_dict = matched_obj.groupdict()
            _range = matched_obj_dict['range']
            key = matched_obj_dict['key']
            _range_list=getRange(_range)
            self.__saveKey(key,_range_list)
            return [re.sub(pattern,repl_func(index),sql) for index,item in enumerate(_range_list)]
        else:
            return [sql]

    def handleComment(self, sql):
        '''
        处理--注释
        '''
        pattern=r'(?#开头和中间的注释)--.*\n|(?#结尾的注释)(?<=;)--.*$'
        return re.sub(pattern,'',sql)

    def handleKey(self,sql):
        '''
        处理变量,需要最后执行
        '''
        def repl_func(matched_obj):
            '''
            正则替换函数
            '''
            matched_obj_dict=matched_obj.groupdict()
            key=matched_obj_dict['key']
            if key not in self.key:
                raise RuntimeError(f'{key}在统一sql中不存在')
            else:
                if isinstance(self.key[key],str) and not self.key[key].startswith('TIMESTAMP'):
                    return f"'{self.key[key]}'"
                else:
                    return self.key[key]

        pattern=r'(?i)<key:(?P<key>[^\s]+?)>'
        return re.sub(pattern,repl_func,sql)


if __name__ == '__main__':
    unisql1 = Unisql(r"E:\Web_AutoTest\Project\金融反洗钱项目\web应用\data\名单管理\道琼斯名单维护.sql", env='oracle', dml='')
    unisql1.test_split_insert_sql()
    res = unisql1.sqlList
    print(f"res:{res}")
    for index, line in enumerate(res):
        print(f"index: {index} ： {line}")
